package com.smartTLC.domain.impl;
import com.smartTLC.dao.DBoperator;
import com.smartTLC.domain.entity.OtherUser;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class OtherUserDaompl implements OtherUserDao{

    public void update(OtherUser user){
    String sql = "update  otheruser set id=?,name=?,username=?,userpwd=?,phonenum=?"
            +"where id=?";
    PreparedStatement pstmt = null;
    DBoperator db = null;
    db = new DBoperator();
        try {
        pstmt = db.getConnection().prepareStatement(sql);
        pstmt.setString(1, user.getId());
        pstmt.setString(2, user.getName());
        pstmt.setString(3, user.getUsername());
        pstmt.setString(4, user.getUserpwd());
        pstmt.setString(5, user.getPhonenum());
        pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        db.close();
    }
    }

    @Override
    public void delete(String student_id) {
        String sql="delete from student_user where student_id=?";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db= new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1,student_id );
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
    }

    @Override
    public void insert(OtherUser user) {
        String sql = "insert into otheruser(id,name,username,userpwd,phonenum) VALUES(?,?,?,?,?) ";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db = new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getUsername());
            pstmt.setString(4, user.getUserpwd());
            pstmt.setString(5, user.getPhonenum());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }

    }

    @Override
    public OtherUser queryById(String id) {
        String sql = "select name,username,userpwd,phonenum" +
                " from otheruser where id=?";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db = new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String names = rs.getString(1);
                String usernames = rs.getString(2);
                String pwd = rs.getString(3);
                String num = rs.getString(4);
                OtherUser user = new OtherUser();
                user.setId(id);
                user.setName(names);
                user.setUsername(usernames);
                user.setUserpwd(pwd);
                user.setPhonenum(num);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return null;
    }


    @Override
    public List queryALL() {
        String sql="select * from otheruser";
        Statement stmt = null;
        DBoperator db = null;
        List list= null;
        try {
            db = new DBoperator();
            stmt = db.getConnection().createStatement();
            ResultSet rs=stmt.executeQuery(sql);
            list = new ArrayList();
            while(rs.next()){
                String id=rs.getString(1);
                String name=rs.getString(2);
                String username=rs.getString(3);
                String userpwd=rs.getString(4);
                String phonenum=rs.getString(5);
                OtherUser user= new OtherUser();
                user.setId(id);
                user.setName(name);
                user.setUsername(username);
                user.setUserpwd(userpwd);
                user.setPhonenum(phonenum);
                list.add(user);

            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }

        return null;
    }

}
